import xlwt
from datetime import datetime, timedelta, time

from odoo import http
from odoo.http import request, content_disposition
from odoo.tools import DEFAULT_SERVER_DATE_FORMAT, DEFAULT_SERVER_DATETIME_FORMAT
from odoo.exceptions import ValidationError


class Controller(http.Controller):

    @http.route('/download/purchase/reconciliation/excel/<string:partner>/<start>/<end>', type='http', auth='user', website=True)
    def download_purchase_reconciliation_excel(self, partner=None, start=None, end=None, **data):
        partner = int(partner)
        if partner:
            partner_id = request.env['res.partner'].browse(partner)
        else:
            partner_id = request.env['res.partner']
        value = self._get_purchase_reconciliation_value(partner_id, start, end)

        if not any([value]):
            raise ValidationError('未找到数据')

        book = xlwt.Workbook(encoding='utf-8')
        sheet = book.add_sheet('Sheet1')

        # 字体大小
        title_font = xlwt.Font()
        title_font.height = 20 * 20

        # 垂直居中
        alignment = xlwt.Alignment()
        alignment.vert = xlwt.Alignment.VERT_CENTER

        # 垂直居中 水平居中
        vertically_horizontally_centered = xlwt.Alignment()
        vertically_horizontally_centered.vert = xlwt.Alignment.VERT_CENTER
        vertically_horizontally_centered.horz = xlwt.Alignment.HORZ_CENTER

        # 标题样式
        title_style = xlwt.XFStyle()
        title_style.font = title_font
        title_style.alignment = vertically_horizontally_centered

        # 副标题样式
        subtitle_style = xlwt.XFStyle()
        subtitle_style.alignment = vertically_horizontally_centered

        # 表头数据样式
        header_style = xlwt.XFStyle()
        header_style.alignment = alignment

        # 行数据明细样式
        field_style = xlwt.XFStyle()

        borders = xlwt.Borders()
        borders.bottom = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN

        field_style.borders = borders
        field_style.alignment = vertically_horizontally_centered

        sheet.write_merge(0, 0, 0, 15, 'WTT入库数量金额明细报表', title_style)

        tall_style = xlwt.easyxf('font:height 520')
        sheet.row(0).set_style(tall_style)

        field_name_list = [
            '唯一值', '入库时间', '订单号码', '供应商', '内部参考号', '产品', '单位', '下单数量', '已接收数量', '未税单价',
            '未税金额', '税率', '税额', '含税金额', '币种', '送货单号', '税票号'
        ]

        for index, name in enumerate(field_name_list):
            sheet.write(1, index, name, field_style)

        i = 2
        for line in value:
            for index, v in enumerate(line):
                sheet.write(i, index, v, field_style)

            i += 1

        sheet.col(1).width = 256 * 20
        sheet.col(2).width = 256 * 20
        sheet.col(3).width = 256 * 20
        sheet.col(4).width = 256 * 30
        sheet.col(5).width = 256 * 40
        # sheet.col(7).width = 256 * 15
        sheet.col(15).width = 256 * 20
        file_name = f'入库明细({partner_id.name or ""}){datetime.now().strftime(DEFAULT_SERVER_DATE_FORMAT)}.xlsx'
        response = request.make_response(None, headers=[
            ('Content-Type', 'application/vnd.ms-excel'),
            ('Content-Disposition', content_disposition(file_name))
        ],
                                         )
        book.save(response.stream)
        return response

    def _get_purchase_reconciliation_value(self, partner, start, end):
        where = ''
        if partner:
            where = f' and rp.id = {partner.id} '
        sql = f"""
            select sml.id,
                   TO_CHAR(sm.date + INTERVAL '7 HOUR', 'YYYY-MM-DD HH24:MI:SS'),
                   po.name,
                   rp.name,
                   pp.default_code,
                   pt.name,
                   uu.name ->> 'zh_CN' pol_uom,
                   pol.product_qty,
                   (case
                       when sml.product_uom_id = pol.product_uom then sml.quantity
                       else sml.quantity * uu.factor end) * (case when sl.usage = 'supplier' then 1 else -1 end),
                   pol.price_unit,
                   pol.price_unit * (case
                       when sml.product_uom_id = pol.product_uom then sml.quantity
                       else sml.quantity * uu.factor end) * (case when sl.usage = 'supplier' then 1 else -1 end) 未税金额,
                   (select string_agg(COALESCE(a.name ->> 'en_US', ''), ',')
                    from account_tax_purchase_order_line_rel atpolr
                             left join account_tax a on atpolr.account_tax_id = a.id
                    where atpolr.purchase_order_line_id = pol.id) 税率,
                pol.price_unit * (case
                       when sml.product_uom_id = pol.product_uom then sml.quantity
                       else sml.quantity * uu.factor end) * (case when sl.usage = 'supplier' then 1 else -1 end) * (select sum(a.amount)
                    from account_tax_purchase_order_line_rel atpolr
                             left join account_tax a on atpolr.account_tax_id = a.id
                    where atpolr.purchase_order_line_id = pol.id) / 100 税额,
                pol.price_unit 
                    * (case when sml.product_uom_id = pol.product_uom then sm.quantity else sm.quantity * uu.factor end) 
                    * (case when sl.usage = 'supplier' then 1 else -1 end) 
                    + pol.price_unit 
                    * (case when sml.product_uom_id = pol.product_uom then sm.quantity else sm.quantity * uu.factor end) 
                    * (case when sl.usage = 'supplier' then 1 else -1 end) 
                    * (select sum(a.amount) from account_tax_purchase_order_line_rel atpolr left join account_tax a on atpolr.account_tax_id = a.id where atpolr.purchase_order_line_id = pol.id) 
                    / 100  含税金额,
                res_currency.name,
            sp.delivery_no
            
            from stock_move sm
                     left join stock_move_line sml on sm.id = sml.move_id
                     left join stock_picking sp on sm.picking_id = sp.id
                     left join purchase_order_line pol on sm.purchase_line_id = pol.id
                     left join purchase_order po on pol.order_id = po.id
                     left join res_partner rp on po.partner_id = rp.id
                     left join product_product pp on sm.product_id = pp.id
                     left join product_template pt on pp.product_tmpl_id = pt.id
                     left join uom_uom uu on pol.product_uom = uu.id
                     left join uom_uom uu2 on sm.product_uom = uu2.id
                     left join res_currency on po.currency_id = res_currency.id
                     left join stock_location sl on sm.location_id = sl.id
                     left join stock_location sl2 on sm.location_dest_id = sl2.id
            where sm.state = 'done'
             {where}
            and sm.date >= '{start}'
            and sm.date < '{end}'
            and sm.purchase_line_id is not null
            order by sm.date 
        """
        request.env.cr.execute(sql)
        result = request.env.cr.fetchall()
        return result or []
